package dao;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.mysql.jdbc.PreparedStatement;
import bean.Traveler;
import exception.DAOException;

public class TravelerDAOImpl extends DAO implements TravelerDAO {

	@Override
	public List<Traveler> viewTravelers() throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		List<Traveler> lstTravelers = new ArrayList<Traveler>();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "select * from ars1.traveler t, ars1.person p where t.id = p.id;";
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				while (resultSet.next()) {
					Traveler traveler = new Traveler();
					traveler.setId(resultSet.getLong("id"));
					traveler.setFirstName(resultSet.getString("first_name"));
					traveler.setLastName(resultSet.getString("last_name"));
					traveler.setUserName(resultSet.getString("username"));
					traveler.setAddressLine1(resultSet.getString("address_line1"));
					traveler.setAddressLine2(resultSet.getString("address_line2"));
					traveler.setCity(resultSet.getString("city"));
					traveler.setState(resultSet.getString("state"));
					traveler.setZip(resultSet.getLong("zip"));
					traveler.setDateOfBirth(resultSet.getString("dob"));
					traveler.setPassportNumber(resultSet.getString("passport_number"));
					traveler.setNationality(resultSet.getString("nationality"));
					traveler.setPassword(resultSet.getString("password"));
					lstTravelers.add(traveler);
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}
		return lstTravelers;
	}


	@Override
	public List<Traveler> viewTravelerByFlight(int flightId)
			throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		List<Traveler> lstTravelers = new ArrayList<Traveler>();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "select * from ars1.journey where flight_id="
						+ flightId + ";";
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				// Journey journey = new Journey();

				while (resultSet.next()) {
					// Flight flight = new Flight();
					Traveler traveler = new Traveler();
					// journey.setFlight(flight);
					// journey.setTraveller(traveler);
					long idt = resultSet.getLong("person_id");
					String SQL1 = "select * from ars1.traveler where id=" + idt
							+ ";";
					stmt = connection.createStatement();
					stmt.setFetchSize(50);
					ResultSet resultSet1 = stmt.executeQuery(SQL1);

					while (resultSet1.next()) {
						traveler.setId(resultSet1.getLong("id"));
						System.out.println(traveler.getFirstName());
						System.out.println(traveler.getId());
						traveler.setNationality(resultSet1
								.getString("nationality"));
						System.out.println(traveler.getNationality());
						traveler.setPassportNumber(resultSet1
								.getString("passport_number"));

						String SQL2 = "select * from ars1.person where id="
								+ idt + ";";
						stmt = connection.createStatement();
						ResultSet resultSet2 = stmt.executeQuery(SQL2);

						while (resultSet2.next()) {
							traveler.setFirstName(resultSet2
									.getString("first_name"));
							System.out.println(traveler.getFirstName());
							traveler.setLastName(resultSet2
									.getString("last_name"));
						}

						lstTravelers.add(traveler);
					}
				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}

		}
		return lstTravelers;
	}

	// This is just s test main to test the methods in this class.
	// May be removed in future
	public static void main(String[] args) throws DAOException {
		TravelerDAO t = new TravelerDAOImpl();
		List<Traveler> t1 = t.viewTravelers();
		System.out.println(t1.get(0).getFirstName());
		System.out.println(t1.get(0).getNationality());
		System.out.println("viewTravelers byFlight");
		List<Traveler> t2 = t.viewTravelerByFlight(23114);
		System.out.println(t2.get(0).getFirstName());
		// System.out.println(t2.get(0).getNationality());

	}

	public boolean createTraveler(String firstName, String lastName,
			String userName, String password, String addressLine1,
			String addressLine2, String city, String state, String dateOfBirth,
			long id, String type, String nationality, String passportNumber,
			long created_by, String created_on, long updated_by,
			String updated_on, long zip) throws DAOException {

		boolean insflag = true;
		Statement stmt = null;
		Connection connection = null;
		try {
			connection = getConnection();
			stmt = connection.createStatement();
			if (connection != null) {


				stmt=connection.createStatement();
				/*	String SQL1 = "insert into person values ('" + firstName + "','" + lastName + "','" + userName +
				"','" + password  + "','" + addressLine1 + "','" + addressLine2 + "','" + city + "','" 
				+ state + "'," + id + ",'" + type + "'," + created_by + "," + created_on + "," +
				updated_by + "," + updated_on + "," + dateOfBirth + " )";
				
				String SQL2 = "insert into employee values ( "+ id + ",'" + workDescription + "'," + 
				hireDate + ",'" + role + "','" + position + ")";  */
		
				/*stmt.addBatch("insert into ars1.person (first_name, last_name, username, password, address_line1," +
						" address_line2, city, state, type, created_by, created_on, updated_by, updated_on, dob, zip ) values ('" + firstName + "','" + lastName + "','" + userName +
						"','" + password  + "','" + addressLine1 + "','" + addressLine2 + "','" + city + "','" 
						+ state + "','" + type + "'," + created_by + ",now()," +
						updated_by + ",now(),'" + dateOfBirth + "', "+zip+")" );
		
				
				stmt.addBatch("insert into ars1.traveler (id, nationality, passport_number)  select max(id), '"+ nationality + "','" + 
				passportNumber + "' from ars1.person");
				stmt.executeBatch();*/

				/*
				 * String SQL1 = "insert into person values ('" + firstName +
				 * "','" + lastName + "','" + userName + "','" + password +
				 * "','" + addressLine1 + "','" + addressLine2 + "','" + city +
				 * "','" + state + "'," + id + ",'" + type + "'," + created_by +
				 * "," + created_on + "," + updated_by + "," + updated_on + ","
				 * + dateOfBirth + " )";
				 * 
				 * String SQL2 = "insert into traveler values ( "+ id + ",'" +
				 * nationality + "'," + passportNumber + ")";
				 */

				stmt.addBatch("insert into ars1.person (first_name, last_name, username, password, address_line1,"
								+ " address_line2, city, state, type, created_by, created_on, updated_by, updated_on, dob, zip ) values ('"
								+ firstName
								+ "','"
								+ lastName
								+ "','"
								+ userName
								+ "','"
								+ password
								+ "','"
								+ addressLine1
								+ "','"
								+ addressLine2
								+ "','"
								+ city
								+ "','"
								+ state
								+ "','"
								+ type
								+ "',"
								+ created_by
								+ ",now(),"
								+ updated_by
								+ ",now(),'" + dateOfBirth + "', " + zip + ")");
				

				stmt
						.addBatch("insert into ars1.traveler (id, nationality, passport_number)  select max(id), '"
								+ nationality
								+ "','"
								+ passportNumber
								+ "' from ars1.person");
				stmt.executeBatch();


			}
		} catch (Exception ex) {
			insflag = false;
			System.out.println(ex.toString());
		}

		finally {
			try{
				stmt.close();
				connection.close();
				return insflag;
			}catch(Exception ex){
				
			}	
		}
		
		return insflag;

		

	}

	public void deleteTraveler(long id) {
		Statement stmt = null;
		Connection connection = null;
		try {
			connection = getConnection();
			if (connection != null) {

				stmt=connection.createStatement();
				stmt.addBatch("delete from Traveler where id=" + id);
				stmt.addBatch("delete from person where id=" + id);
				stmt.executeBatch();
				System.out.println(stmt);

				stmt = connection.createStatement();
				stmt.addBatch("delete from traveler where id=" + id);
				// stmt.executeUpdate();

				stmt.addBatch("delete from person where id=" + id);
				stmt.executeBatch();
				// System.out.println(stmt);

			}
		} catch (Exception ex) {
			System.out.println(ex.toString());
		}

		finally {
			try{
				stmt.close();
				connection.close();
			}catch(Exception ex){
				
			}	
		}


		

	}

	public void updateTraveler(String firstName, String lastName,
			String userName, String password, String addressLine1,
			String addressLine2, String city, String state, String dateOfBirth,
			long id, String type, String nationality, String passportNumber,
			long created_by, String created_on, long updated_by,

			String updated_on, long zip) throws DAOException {


		Statement stmt = null;
		Connection connection = null;

		try {
			connection = getConnection();
			stmt = connection.createStatement();
			if (connection != null) {
				stmt=connection.createStatement();
				stmt.addBatch("update person set first_name='"+ firstName + "', last_name= '" 
						+ lastName + "', username='" + userName + "', password='" + password + "', address_line1= '"
						+ addressLine1 + "', address_line2='" + addressLine2 + "', city='" + city + "', state='" 
						+ state + "', updated_by=" + updated_by + ", updated_on=now(), zip="+zip+", dob='"+dateOfBirth+"' where id=" 
						+ id);


				stmt.addBatch("update Traveler set passport_number='"
								+ passportNumber
								+ "', nationality='"
								+ nationality + "' where id=" + id);
				stmt.executeBatch();
				System.out.println(stmt);

				stmt.addBatch("update person set first_name='" + firstName
						+ "', last_name= '" + lastName + "', username='"
						+ userName + "', password='" + password
						+ "', address_line1= '" + addressLine1
						+ "', address_line2='" + addressLine2 + "', city='"
						+ city + "', state='" + state + "', updated_by="
						+ updated_by + ", updated_on=now(), zip=" + zip
						+ " where id=" + id);
				// stmt.executeUpdate();

				stmt.addBatch("update traveler set nationality='" + nationality
						+ "', passport_number='" + passportNumber
						+ "' where id=" + id);
				stmt.executeBatch();
				System.out.println("update traveler set nationality='"
						+ nationality + "', passport_number='" + passportNumber
						+ "' where id=" + id);

			}
		} catch (Exception ex) {
			System.out.println(ex.toString());
		}

		finally {
			try{
				stmt.close();
				connection.close();
			}catch(Exception ex){
				
			}	
		}



	}

	


	@Override
	public Traveler getTravelerDetails(long id) throws DAOException {
		Statement stmt = null;
		Connection connection = null;
		Traveler trvl = new Traveler();
		try {
			connection = getConnection();
			if (connection != null) {
				String SQL = "SELECT * FROM ars1.person p, ars1.traveler t where p.id = t.id and p.id="
						+ id;
				stmt = connection.createStatement();
				stmt.setFetchSize(50);
				ResultSet resultSet = stmt.executeQuery(SQL);
				while (resultSet.next()) {
					trvl.setId(resultSet.getLong("id"));
					trvl.setFirstName(resultSet.getString("first_name"));
					trvl.setLastName(resultSet.getString("last_name"));
					trvl.setUserName(resultSet.getString("username"));
					trvl.setAddressLine1(resultSet.getString("address_line1"));
					trvl.setAddressLine2(resultSet.getString("address_line2"));
					trvl.setCity(resultSet.getString("city"));
					trvl.setState(resultSet.getString("state"));
					// trvl.setZip(resultSet.getLong("zip"));
					// trvl.setDateOfBirth(resultSet.getString("dob"));
					trvl.setNationality(resultSet.getString("nationality"));
					trvl.setDateOfBirth(resultSet.getString("dob"));
					trvl.setPassportNumber(resultSet
							.getString("passport_number"));

					trvl.setPassword(resultSet.getString("password"));

				}

			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} // Very important step. Never miss out on this!
		finally {
			try {
				stmt.close();
				connection.close();
			} catch (Exception ex) {

			}
		}
		return trvl;
	}


}
